--POUR CONTROLE


define erp='erp_mli2.';
define outils='outils_mli2.';


/*SELECT DISTINCT SubStr(t.id,2,3),TYPCTAR,DEVISE,CODVAR,ATTMSQ
FROM uv_ctarifs t, uv_ctarifs_codvar v, uv_ctarifs_attmsq a
WHERE t.TYPCTAR ='V' AND devise ='EUR'
  AND v.id=t.id
  AND a.id=v.id AND a.rangmulti2=v.rangmulti2
ORDER BY 1,2,3,4,5  */


--CREATE TABLE se_ctr_optiondet AS (SELECT * FROM &erp.ctr_optiondet WHERE 1=2);

/*
select * from (
SELECT &outils.stragg(source) source,option_cod, optiondet_id, vrt_cod, att_cod, chap_cod, classe_cod, enreg_id, rang
FROM (
  SELECT 'uv' source ,g.* FROM se_ctr_optiondet g
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.ctr_optiondet g
  )
GROUP BY option_cod, optiondet_id, vrt_cod, att_cod, chap_cod, classe_cod, enreg_id, rang
HAVING Count(1)!=2) r
ORDER BY option_cod,vrt_cod,att_cod*/


DELETE FROM se_ctr_optiondet;

 INSERT INTO se_ctr_optiondet
 (SELECT  id option_cod,
          attmsq optiondet_id,
          codvar vrt_cod,
          attmsq att_cod,
          NULL chap_cod,
          NULL classe_cod,
          0 enreg_id,
          attmsq rang
    FROM
    (SELECT DISTINCT SubStr(t.id,2,3) id,TYPCTAR,DEVISE,CODVAR,ATTMSQ
      FROM uv_ctarifs t, uv_ctarifs_codvar v, uv_ctarifs_attmsq a
      WHERE t.TYPCTAR ='V' AND devise ='EUR'
        AND v.id=t.id
        AND a.id=v.id AND a.rangmulti2=v.rangmulti2)

    ) ;

 UPDATE se_ctr_optiondet tf SET enreg_id=(select Min(enreg_id) FROM &erp.ctr_optiondet f WHERE f.option_cod=tf.option_cod AND f.VRT_COD=tf.vrt_cod AND f.ATT_COD=tf.att_cod) ;
 UPDATE se_ctr_optiondet tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;



 CALL uvse_synchroverserp('option_cod@optiondet_id','ctr_optiondet','O') ;

 commit;
